查詢最佳化是一個成本高昂的處理程序。因此，SQL Server 會將已建立的執行計畫保留在記憶體中，以便後續重複使用。這些執行計畫所存放的記憶體區域稱為執行計畫快取（Plan Cache）。

查詢執行計畫快取

要取得這個資訊，首先要去看 DMV

SELECT
    decp.refcounts      AS N'快取物件參考次數',
    decp.usecounts      AS N'執行計畫使用次數',
    decp.size_in_bytes  AS N'快取大小（位元組）',
    decp.cacheobjtype   AS N'快取物件類型',
    decp.objtype        AS N'物件類型',
    decp.plan_handle    AS N'執行計畫控制代碼'
FROM sys.dm_exec_cached_plans AS decp;

此 DMV 可與其他 DMV 搭配使用，以取得更多資訊。例如

• 可從 sys.dm_exec_requests 取得目前正在執行之要求的效能指標
• 從 sys.dm_exec_query_stats 取得彙總統計資料
• 以及如前所述，透過 sys.dm_exec_query_plan 取得執行計畫

執行計畫快取&重複使用

查詢一提交至 SQL Server，SQL Server 在完成語法檢查後，便會到執行計畫快取中尋找相符的執行計畫。

如果找不到就會依照前面說的流程進行最佳化。

如果QUERY STORE 存在強制執行計畫，就會用強制執行計畫去做。

不管是什麼查詢，通常都應該提供 WHERE 篩選條件，用來限制結果的範圍。資料篩選的方式會直接影響執行計畫是否能夠重複使用。

一般來說要遵守，只移動需要的資料，而且只在需要時後移動，這項原則

概括而言，SQL Server 中的查詢分為兩大類，這些分類會影響執行計畫何時以及如何被重複使用 :

• Ad hoc
• Prepared

接下來要開始說明這兩個東西，為了示範，會用很多次手動把執行計畫從快取中移除，要達成這個目的有以下兩種辦法 :

• DBCC FREEPROCCACHE
  這個會清除所有資料庫中的快取執行計畫，整個 INSTANCE 裡面的都移除

• ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE
  這個會只移除當前資料庫中的快取執行計畫

2019 以後可以加入 plan_handle，只從快取中移除單一執行計畫。
正式環境中，執行 DBCC FREEPROCCACHE，如果沒有加 plan_handle，會嚴重影響效能，謹慎使用。

Ad Hoc

不論 where 用寫死的常數或是變數，都稱作 Ad Hoc 查詢。
到目前為止，所有的範例，幾乎都是 Ad Hoc 查詢

--類似像這種查詢就是 Ad Hoc
SELECT soh.SalesOrderNumber,
       soh.OrderDate,
       sod.OrderQty,
       sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
    ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID = 29690
  AND sod.ProductID = 711;
--像這種 where 寫死的，代表他不會變動，每次查詢都會用相同的值
--這看起來像是一句廢話，但後面就會知道這不是廢話

然後即使這些寫死的改成區域變數，也不等同於建立參數化查詢。

只要這些值發生變動，基本上就會產生新的執行計畫，對 SQL Server 而言，查詢本身已不再完全相同。

快取執行計畫這個東西如果要重複使用，T-SQL 文字要完全一致，多一個空格、換行都不行，要完全一模一樣，包括註解也要一模一樣。

--取得 Ad Hoc 查詢的相關資訊
SELECT
    c.usecounts     AS N'使用次數',
    c.cacheobjtype  AS N'快取物件類型',
    c.objtype       AS N'物件類型',
    t.text          AS N'SQL 查詢文字'
FROM sys.dm_exec_cached_plans AS c
CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) AS t
WHERE t.text LIKE N'%SalesOrderHeader%'
  AND t.text LIKE N'%CustomerID = 29690%'
  AND t.text LIKE N'%ProductID = 711%';

但是我們可以對於部分 adhoc 查詢，透過定的方式去改變這種只要改一個字就要重新編譯執行計畫的快取行為。

針對 Ad Hoc 最佳化

adhoc 記憶體

當系統存在大量 adhoc 查詢，可以透過啟用 optimize for ad hoc workloads 設定來改善執行計畫快取的使用情況。

這個設定是 Instance 層級。會改變 SQL Server 處理 Ad Hoc 查詢的方式。

查詢第一次完成編譯後，SQL Server 不會將完整執行計畫儲存在快取中，而是只儲存一個執行計畫存根（plan stub）。

EXEC sys.sp_configure 'show advanced options', '1';
GO
RECONFIGURE;
GO
--啟用ad hoc 最佳化
EXEC sys.sp_configure 'optimize for ad hoc workloads', 1;
GO
RECONFIGURE;
--清除執行計畫快取
DBCC FREEPROCCACHE;

--再跑一次
SELECT soh.SalesOrderNumber,
       soh.OrderDate,
       sod.OrderQty,
       sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
    ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID = 29690
  AND sod.ProductID = 711;

--然後看一下ADHOC
SELECT
    c.usecounts     AS N'使用次數',
    c.cacheobjtype  AS N'快取物件類型',
    c.objtype       AS N'物件類型',
    c.size_in_bytes AS N'大小',
    t.text          AS N'SQL 查詢文字'

FROM sys.dm_exec_cached_plans AS c
CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) AS t
WHERE t.text LIKE N'%SalesOrderHeader%'
  AND t.text LIKE N'%CustomerID = 29690%'
  AND t.text LIKE N'%ProductID = 711%';

可以看到此時快取物件類型是 : Compiled Plan Stub，這是代表為了節省快取記憶體，只留下識別用的存根。第一次只快取 stub，同一個語法再次執行，stub 就會被換成 complied plan。

所以啟用 optimize for ad hoc workloads 的好處是如果今天是一個大量只會使用一次的查詢，可以節省非常多記憶體，這一次也才幾百 byte。

這裡適合做這種優化的系統是 :

• 大量 Ad Hoc 查詢
• 每次查詢只執行一次
• 查詢文字或常值經常變動
• 快取中有大量 usecounts = 1 的完整計畫

--跑第二次
SELECT soh.SalesOrderNumber,
       soh.OrderDate,
       sod.OrderQty,
       sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
    ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID = 29690
  AND sod.ProductID = 711;
 
 SELECT
    c.usecounts     AS N'使用次數',
    c.cacheobjtype  AS N'快取物件類型',
    c.objtype       AS N'物件類型',
    c.size_in_bytes AS N'大小',
    t.text          AS N'SQL 查詢文字'
FROM sys.dm_exec_cached_plans AS c
CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) AS t
WHERE t.text LIKE N'%SalesOrderHeader%'
  AND t.text LIKE N'%CustomerID = 29690%'
  AND t.text LIKE N'%ProductID = 711%';

占用記憶體的量就非常明顯的差距。

--為了後續的內容，先把這功能關掉
EXEC sys.sp_configure 'optimize for ad hoc workloads', 0;
GO

RECONFIGURE;
GO

EXEC sys.sp_configure 'show advanced options', 0;
GO

RECONFIGURE;

以下是我個人建議

大多數系統都應該啟用這個設定，因為它並沒有什麼副作用。

第二次查詢會產生額外的成本，因為要重新編譯，不過本來第一次就會要編譯了，這只是把成本往後移動，如果有那種只做一次的查詢，剛好可以受益。

簡單參數化

查詢最佳化流程的一部分，是由最佳化工具檢查查詢內容，判斷是否可以用參數取代寫死的常數。

我記得我有再前面示範過。

當最佳化工具辨識出適合進行這種處理的情況的時候，就會透過一個叫做簡單參數化的流程，把查詢參數化。

在簡單參數化過程中，最佳化工具會確認：若將 Ad Hoc 查詢參數化，參數值的變化不會使執行計畫產生大幅改變。

如果判定參數化是安全的，SQL Server 就會修改查詢的實際結構，並根據重新建構後的查詢產生執行計畫。

SELECT
    a.AddressLine1    AS N'地址第一行',
    a.City            AS N'城市',
    a.StateProvinceID AS N'州省識別碼'
FROM Person.Address AS a
WHERE a.AddressID = 42;

 SELECT
    c.usecounts     AS N'使用次數',
    c.cacheobjtype  AS N'快取物件類型',
    c.objtype       AS N'物件類型',
    c.size_in_bytes AS N'大小',
    t.text          AS N'SQL 查詢文字'
FROM sys.dm_exec_cached_plans AS c
CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) AS t

這裡有兩個關鍵 :
多了一個參數定義 ( @1 tinyint )
還有物件類型變成 prepared

但是同時也會看到上面還有一個 adhoc 這物件，那個他其實不是一個完整的獨立實際執行計畫，他像是一個參數化查詢的入口，真正提供不同參數值重複使用的計畫是第二個 prepared。

至於 tinyint 那個是根據輸入的參數自動給的，如果今天把數字放大，那這個執行計畫就會再變一次。

雖然這種方式會增加執行計畫快取記憶體使用量，但相較於為了每一個 adhoc 多儲存一份完整執行計畫，所增加的成本會小很多。

--再去跑一次這個，會看到他會去用參數化的執行計畫如此就可以節省時間
SELECT
    a.AddressLine1    AS N'地址第一行',
    a.City            AS N'城市',
    a.StateProvinceID AS N'州省識別碼'
FROM Person.Address AS a
WHERE a.AddressID = 56;

這東西其實 sql server 做的很保守，稍微複雜一點的查詢他就會直接不使用參數話查詢。實際看到簡單參數化的機率很低。

強制參數化

參數化會影響的是執行計畫能不能重複使用，也有可能可以帶來更好的效能，因為節省了記憶體。

不過參數化也可能引發其他問題，這會全部在參數嗅探的時候進一步說明。

如果現在系統存在大量 Adhoc 查詢，考慮提高自動參數化的查詢數量，是一個做法。

SQL Server有提供一個資料庫層級的設定，可以嘗試讓更多查詢進入自動參數化流程，這個功能就叫做強制參數化。

--開啟強制參數化
ALTER DATABASE AdventureWorks2022 SET
PARAMETERIZATION FORCED;

--測試一個稍微複雜一點的查詢
SELECT
    ea.EmailAddress        AS N'電子郵件地址',
    e.BirthDate            AS N'出生日期',
    a.City                 AS N'城市'
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
    ON p.BusinessEntityID = e.BusinessEntityID
JOIN Person.BusinessEntityAddress AS bea
    ON e.BusinessEntityID = bea.BusinessEntityID
JOIN Person.Address AS a
    ON bea.AddressID = a.AddressID
JOIN Person.StateProvince AS sp
    ON a.StateProvinceID = sp.StateProvinceID
JOIN Person.EmailAddress AS ea
    ON p.BusinessEntityID = ea.BusinessEntityID
WHERE ea.EmailAddress LIKE 'david%'
  AND sp.StateProvinceCode = 'WA';

 SELECT
    c.usecounts     AS N'使用次數',
    c.cacheobjtype  AS N'快取物件類型',
    c.objtype       AS N'物件類型',
    c.size_in_bytes AS N'大小',
    t.text          AS N'SQL 查詢文字'
FROM sys.dm_exec_cached_plans AS c
CROSS APPLY sys.dm_exec_sql_text(c.plan_handle) AS t

然後去看執行計畫類型，他就會寫這是 prepared

仔細看的話，這個查詢有兩個常值，但是在參數化的過程中， LIKE ‘david%’ 的部分並沒有被參數化，而這是強制參數化運作的多項限制之一。

以下是強制參數化的一些限制 :

• INSERT…EXECUTE 查詢
• sp、trigger、自訂 function ，因為這本來就屬於 PREPARED 了
• 用戶端自己建立的 PREPARED，自己建的就不會參予強制參數化。
• 使用 RECOMPILE
• LIKE 裡面用的比對模式跟 ESCAPE 子句引數
• ….etc 這有太多了，完整清單在微軟的文件立，不過上面這些項目可以大致了解哪些類型的查詢沒辦法用強制參數化。

--停用強制參數化
ALTER DATABASE AdventureWorks2022 SET
PARAMETERIZATION SIMPLE;

Prepared

被定義成 prepared 的查詢，會把參數做為執行流程中的核心要素

prepared 的核心概念是，在不修改程式碼的情況下改變查詢結果。

建立 prepared 有三種方式

• 預存程序（Stored Procedures）
  預存程序可包含一個或多個 T-SQL 陳述式，並可接收及回傳由使用者定義與傳入的參數。
• sp_executesql
  此 SQL Server 命令可執行包含參數的 T-SQL 陳述式，而不需要將該陳述式定義為預存程序。sp_executesql 也可以在預存程序內執行。
• Prepare／Execute 模型
  允許用戶端應用程式要求 SQL Server 產生可重複使用的查詢執行計畫，後續再透過不同參數值執行該查詢，運作方式類似預存程序。這是 Entity Framework 等物件關聯對應工具（Object-Relational Mapping，ORM）常見的做法。

預存程序

這是提升執行計畫快取使用效率的標準做法之一。

預存程序會在執行時進行編譯，不是建立時。這與原生編譯預存程序不同。

原生編譯預存程序是記憶體表的特殊預存程序，不再此討論。

預存程序中的每一個 SQL 陳述式都會產生對應的執行計畫。之後，當預存程序以不同參數值再次被呼叫時，該執行計畫便可重複使用。

除了用 sys.dm_exec_cached_plans以外，也可以透過擴充事件來追蹤執行計畫快取行為。

--為了後面測試，這邊隨便建立一個sp 
CREATE OR ALTER PROCEDURE dbo.BasicSalesInfo
    @ProductID INT,
    @CustomerID INT
AS
SELECT
    soh.SalesOrderNumber,
    soh.OrderDate,
    sod.OrderQty,
    sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
    ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID = @CustomerID
  AND sod.ProductID = @ProductID;

擴充事件可以去擷取這三個東西

• rpc_starting 開始
• sp_cache_insert : sp的執行計畫加入快取
• rpc_completed : sp 完成

這三個東西會按照順序執行

我們在執行預存程序的時候會有兩層

第一層 : 是先處理送進去的整段批次，也就是 EXEC SP....這種東西

第二層 : 這層才是 SP 本身裡面的內容

所以在擴充事件會看到

sp_cache_miss

sp_cache_hit

這兩個事件不一定是在講同一個快取物件，因為 miss 沒有命中，她可能是在說沒有命中外層的那個批次快取

預存程序還有幾個特性 :

• 何時編譯?
• 提升效能的機制
• 效能以外的優點

何時編譯?

有人會認為，預存程序在建立的時候就進行編譯，但這實際上並不準確

CREATE PROCEDURE 她只會到剖析這步驟，不會到最佳化，所以她不會編譯。

一直到第一次執行預存程序的時候她才會編譯

預存程序的效能優勢

如果你單純的用 SP，那她的效能跟一般的查詢並不會有任何不同，她確實是可以省下重新編譯跟可以重複使用執行計畫獲得效益，阿但是 PREPARED 的陳述式同樣也可以做到這一點。

此外，預存程序也可能受益於參數嗅探。SQL Server 在編譯時，會使用傳入預存程序的參數值，搭配統計資料產生更具針對性的執行計畫。

一樣，參數嗅探不會在這裡大篇幅說明，以後會寫

使用預存程序可以帶來以下效能改善：

• 讓與資料密切相關的商業邏輯靠近資料執行
  常有人認為商業邏輯不應放在資料庫中。然而，有些商業邏輯與資料高度相關，應直接在資料儲存的位置執行，而不是先將大量資料搬移到其他層處理。
  因此，彙總運算及其他集合式操作，如果能在存取資料時直接完成，就很適合放在預存程序中。
• 降低網路流量
  在 SQL Server 引擎內直接篩選資料，可以減少經由網路傳輸的資料量。
  此外，呼叫預存程序通常只需要傳送程序名稱與必要參數，相較於傳送大型 T-SQL 批次，也能進一步降低網路流量。

預存程序的非效能優勢

• 讓應用程式與資料結構變更隔離
  透過預存程序存取資料，可以在不必同步修改應用程式程式碼的情況下，調整底層資料結構。這些結構上的變更可由預存程序負責處理，而不必直接反映到應用程式。
• 提供集中管理的單一位置
  實作於預存程序中的商業邏輯，會作為資料庫的一部分進行維護。這表示程式碼、存取權限與相關行為，都可以直接由資料庫集中管理。
  不過，這並非在所有情況下都適合，就如同把所有資料存取邏輯都放在應用程式端，也不一定理想。
• 提升安全性
  可以限制使用者直接存取資料表的權限，只授予其執行預存程序、檢視表或其他類似物件的權限。透過這種方式，可以隱藏底層資料結構，進一步提升安全性。

預存程序並不是萬靈丹。它仍會為系統帶來額外成本，尤其是在儲存與維護方面。

某些系統中的查詢可能只執行一次，或只會執行極少次。在這種情況下，為每個查詢建立預存程序可能並不划算，也會增加維護難度。此時，使用 sp_executesql 可能會更合適。

sp_executesql

這事系統的預存程序，她可以把 adhoc 查詢改成用 prepared 工作

--類似這樣 參數化查詢
--你這個查完再去用上面的dmv方法去看 就會看到這事 prepared 工作
--然後就算把參數 711 換掉，她一就會透過一樣的執行計畫查詢
DECLARE
    @query     NVARCHAR(MAX),
    @paramlist NVARCHAR(MAX);

SET @query =
N'SELECT
      soh.SalesOrderNumber,
      soh.OrderDate,
      sod.OrderQty,
      sod.LineTotal
  FROM Sales.SalesOrderHeader AS soh
  JOIN Sales.SalesOrderDetail AS sod
      ON soh.SalesOrderID = sod.SalesOrderID
  WHERE soh.CustomerID = @CustomerID
    AND sod.ProductID = @ProductID';

SET @paramlist =
N'@CustomerID INT,
  @ProductID INT';

EXEC sys.sp_executesql
    @query,
    @paramlist,
    @CustomerID = 29690,
    @ProductID = 711;

使用 sp_executesql 主要有兩個目的。

第一，也是最重要的目的，是讓批次查詢也能具備一定程度的執行計畫重複使用能力

第二，透過明確定義參數及其資料型別，可以避免將參數值直接串接至 SQL 字串中，進而降低批次處理遭受 SQL Injection 攻擊的風險。

但是在 SQL Server 2025 以前，sp_executesql 存在一項明顯弱點：編譯流程不像預存程序一樣序列化。

這表示當多個不同工作階段同時嘗試執行相同且尚未快取的查詢時，可能會同時進行編譯，形成所謂的compile storm

2025 則是提供了一個資料庫組態 :

OPTIMIZED_SP_EXECUTESQL

啟用後，sp_executesql 的編譯流程會像預存程序一樣序列化。當多個工作階段同時提交相同查詢時，只允許其中一個工作階段進行編譯，其他工作階段等待並在計畫建立後重複使用該計畫。

這可降低多個相同陳述式同時編譯所造成的快取膨脹與快取顛簸。此設定預設為停用

--啟用
ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZED_SP_EXECUTESQL = ON;
--另外微軟也建議如果啟用這個功能的話，同時也去起用非同步統計資料更新

--啟用非同步自動更新統計資料
ALTER DATABASE CURRENT
SET AUTO_UPDATE_STATISTICS_ASYNC ON;

--以及啟用 ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY
--改用低優先權佇列等待
ALTER DATABASE SCOPED CONFIGURATION
SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY = ON;

Query Hash 和 Query Plan Hash

SQL Server 會分別為查詢跟執行計畫去產生哈希直。

這個非常適合用來搜尋可能具有相似的查詢跟執行計畫。

因為一般後端開發人員經常會在程式碼中使用固定的模式，然後這些模式就會產生結構相似的查詢或相似的執行計畫。

所以如果今天確定某一個執行計畫是良好的，可以透過哈希直的方式快速找到其他相似的執行計畫，然後在相同的地方做修改。

query_hash 跟 query_plan_hash 會存在下面的 dmv 中 :
sys.dm_exec_query_stats
sys.dm_exec_requests

這些哈析值也會存在 query store 中。另外，部分的擴充事件也會直接提供這些值，或可以將他們加入 action 中。

不過，與所有雜湊值一樣，仍可能發生雜湊碰撞，也就是實際上不同的查詢或執行計畫，產生相同的雜湊值。因此，雖然這些值很有用，但不能將它們視為查詢或執行計畫的唯一識別碼。

SELECT
    p.Name  AS ProductName,
    ps.Name AS SubCategory,
    pc.Name AS Category
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS ps
    ON p.ProductSubcategoryID = ps.ProductSubcategoryID
JOIN Production.ProductCategory AS pc
    ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE pc.Name = 'Bikes'
  AND ps.Name = 'Touring Bikes';

SELECT
    p.Name  AS ProductName,
    ps.Name AS SubCategory,
    pc.Name AS Category
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS ps
    ON p.ProductSubcategoryID = ps.ProductSubcategoryID
JOIN Production.ProductCategory AS pc
    ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE pc.Name = 'Bikes'
  AND ps.Name = 'Road Bikes';
  
  SELECT
    deqs.execution_count,
    deqs.query_hash,
    deqs.query_plan_hash,
    dest.text
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS dest
WHERE dest.text LIKE '%ProductName%';

在強制參數化停用的狀況下，這兩個查詢已經複雜到不會套用簡單參數化了。

因此，兩個僅僅只是 where 條件不同，卻都各自產生一份不同的執行計畫。

不過可以看到 query_plan_hash 相同

這時候如果再拿掉兩個欄位，去看執行計畫她還是會一模一樣

--拿掉兩個欄位
SELECT
    p.Name  AS ProductName
FROM Production.Product AS p
JOIN Production.ProductSubcategory AS ps
    ON p.ProductSubcategoryID = ps.ProductSubcategoryID
JOIN Production.ProductCategory AS pc
    ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE pc.Name = 'Bikes'
  AND ps.Name = 'Touring Bikes';

現在可以看出，使用雜湊值搜尋查詢具有實際用途。假設這些查詢的效能不佳，而且已經找出改善方式，就可以快速搜尋相同的 query_plan_hash，找出所有需要套用相同修正的查詢位置。

但是我們當然也會有相反的情況

SELECT
    p.Name,
    tha.TransactionDate,
    tha.TransactionType,
    tha.Quantity,
    tha.ActualCost
FROM Production.TransactionHistoryArchive AS tha
JOIN Production.Product AS p
    ON tha.ProductID = p.ProductID
WHERE p.ProductID = 461;

SELECT
    p.Name,
    tha.TransactionDate,
    tha.TransactionType,
    tha.Quantity,
    tha.ActualCost
FROM Production.TransactionHistoryArchive AS tha
JOIN Production.Product AS p
    ON tha.ProductID = p.ProductID
WHERE p.ProductID = 712;

  SELECT
    deqs.execution_count,
    deqs.query_hash,
    deqs.query_plan_hash,
    dest.text
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS dest

這兩個查詢很相似，但是QUERY_PLAN_HASH 確不一樣，反倒是文字變動太少不足以變更 query_hash值。

但是因為 where 傳入不同值的差異，導致 sql server 產生了結構差異極大的執行計畫。

這裡會有差異是因為 = 461 預估只有3 筆，所以她用 SEEK + LOOKUP

但是 = 712 預估 1034 筆 所以她用SCAN

執行計畫快取建議

前面說的那些，其實絕大多是都是在盡可能的讓執行計畫可以重複利用，或是只用一次的執行計畫就不要佔用太多的記憶體。
所以在這裡的核心原則就是，盡可能地重複使用執行計畫，以達到降低編譯執行計畫所產生的額外成本。

為了達到這個目的，查詢的 T-SQL 編寫方式必須能讓執行計畫被重複利用，可行的情況下，盡量避免 AD HOC 查詢，改採用 Prepared 陳述式。

那些下來有幾項我對於這個議題上面的建議

• 明確將查詢中的常值參數化。
• 可行時使用預存程序。
• 使用 sp_executesql 作為預存程序的替代方案。
• 善用 Prepare／Execute 模型。
• 避免使用 Ad Hoc 查詢。
• 啟用 Optimize for Ad Hoc Workloads。
• 動態查詢應優先使用 sp_executesql，而不是 EXECUTE。

明確將查詢中的常值參數化

同一個查詢經常會重複執行，只是 WHERE 子句中使用的值不同。

透過參數化查詢，將查詢中的固定部分與變動部分分離，可以提高執行計畫的重複使用率。

雖然可以使用簡單參數化或強制參數化，但這兩種機制都有其限制。明確將工作負載中的常值參數化，有助於提高執行計畫重複使用率，並減少快取中的執行計畫數量。

不過需要注意，有些執行計畫會對傳入的參數值非常敏感，可能因此產生其他問題。

例如前面說過的統計資訊有可能不準確。這是參數嗅探的問題後面再說

可行時使用預存程序

在預存程序中使用參數化查詢，是協助管理執行計畫快取的有效方式。

呼叫預存程序時，除了預存程序名稱之外，通常只需傳送參數，因此相較於 Ad Hoc 查詢，可降低網路流量。

由於預存程序可從快取中重複使用執行計畫，因此在許多情況下，其執行速度會比每次都必須重新編譯的 Ad Hoc 查詢更快。

不過，預存程序也可能被過度使用。有些商業流程適合放在資料庫中處理，但也有些商業流程不應放在資料庫中。

使用 sp_executesql 作為預存程序的替代方案

某些情況下，建立預存程序並不合理。此時可以使用 sp_executesql，讓查詢仍以 Prepared 工作負載的形式執行。

使用 sp_executesql 時，必須將完整查詢文字傳入。當查詢非常複雜或篇幅很長時，這種寫法可能較難維護。

透過參數搭配 sp_executesql，也可以降低 SQL Injection 攻擊的風險。

雖然可以使用 EXECUTE 執行 Ad Hoc 字串，但不建議這樣做。主要原因有兩個：

• 無法有效將查詢參數化
• 很容易引入 SQL Injection 攻擊風險

若必須在程式碼中動態組合並執行 SQL 字串，應使用 sp_executesql。

反正呢就是不要寫這種.

-- 這種寫法算是 ad hoc
DECLARE @CustomerID NVARCHAR(100) = N'29690';

DECLARE @sql NVARCHAR(MAX) =
    N'SELECT *
      FROM Sales.SalesOrderHeader
      WHERE CustomerID = ' + @CustomerID;

EXEC(@sql);

改成習慣去寫這種

--這種寫法才是 prepared，可以重複使用執行計畫
DECLARE @sql NVARCHAR(MAX);

SET @sql =
    N'SELECT *
      FROM Sales.SalesOrderHeader
      WHERE CustomerID = @CustomerID;';

EXEC sys.sp_executesql
    @sql,
    N'@CustomerID INT',
    @CustomerID = 29690;

善用 Prepare／Execute 模型

如果應用程式會先建立查詢字串，再透過網路傳送給 SQL Server 執行，可以考慮使用 Prepare／Execute 模型，而不是每次都透過 sp_executesql 傳送完整查詢。

使用此模型時，完整查詢字串只需透過網路傳送一次。之後只需要使用執行計畫控制代碼及參數值執行查詢。

但這個是屬於後端程式語言的範疇，並沒有在這裡多琢磨，但是我可以提供一個C#範例

using var command = connection.CreateCommand();

command.CommandText = """
    SELECT *
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID;
    """;

var customerId = command.CreateParameter();
customerId.ParameterName = "@CustomerID";
customerId.DbType = System.Data.DbType.Int32;
command.Parameters.Add(customerId);

command.Prepare();

customerId.Value = 29690;
using var result1 = command.ExecuteReader();

customerId.Value = 29500;
using var result2 = command.ExecuteReader();

避免 Ad Hoc 查詢

大多數系統都難以完全避免 Ad Hoc 查詢，但應盡可能減少其數量。

Ad Hoc 查詢通常較難重複使用執行計畫，因此會增加額外的編譯成本，也可能造成執行計畫快取膨脹，使快取中充滿缺乏重複使用價值的執行計畫。

啟用 Optimize for Ad Hoc Workloads

由於 Ad Hoc 查詢在多數系統中似乎難以完全避免，因此應盡可能避免讓它們大量占用執行計畫快取。

可以透過啟用 Optimize for Ad Hoc Workloads 達成這個目的。啟用後，查詢第一次執行時，快取中只會保留一個較小的執行計畫存根；只有當相同查詢再次執行時，完整執行計畫才會進入快取。